"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 关闭session 删除预备语句，不可查询到已删除预备语句
Create At   : 2023/12/12
Owner       : ningyali
Description :
    1.建表
    2.新建session1，创建预备语句
    3.新建session2，查询视图PG_PREPARED_STATEMENT
    4.关闭session1
    5.再次查询视图PG_PREPARED_STATEMENT
    6.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功，可查询到stmt1
    4.执行成功
    5.执行成功，查询结果中没有stmt1
    6.执行成功
History     :
    2023-12-26 ningyali session1线程启动后等待1s,以确保查询预备语句时预备语句已创建
"""

import os
import time
import unittest

from yat.test import macro

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement32(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_prepare_32'
        self.prepare_name = 'stmt'
        self.prepare_sql = f"prepare {self.prepare_name} as " \
            f"insert into {self.t_name} values(1, 'openGauss');" \
            f"select pg_sleep(3);"
        self.query_sql = 'select pg_prepared_statement(sessionid) ' \
                         'from pg_stat_activity;' \
                         'select * from pg_prepared_statement(0);'

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:新建session1，创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(f'{self.prepare_sql}',))
        session1.setDaemon(True)
        session1.start()
        time.sleep(1)

        text = 'step3:新建session2，查询视图PG_PREPARED_STATEMENT; ' \
               'expect:执行成功,可查询到stmt1'
        self.log.info(f'-----{text}-----')
        res = self.pri_sh.execut_db_sql(self.query_sql)
        self.log.info(res)
        self.assertEqual(res.count(self.prepare_name), 4, f'执行失败：{text}')

        text = 'step4:关闭session1; expect:执行成功'
        self.log.info(f'-----{text}-----')
        session1.join(180)
        res1 = session1.get_result()
        self.log.info(f'res1:\n{res1}')
        self.assertIn('PREPARE', res1, f'执行失败：{text}')

        text = 'step5:再次查询视图PG_PREPARED_STATEMENT; ' \
               'expect:执行成功,查询结果中没有stmt1'
        self.log.info(f'-----{text}-----')
        res = self.pri_sh.execut_db_sql(self.query_sql)
        self.log.info(res)
        self.assertEqual(res.count(self.prepare_name), 0, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text = 'step6:清理环境; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name} cascade;'
        res = self.pri_sh.execut_db_sql(sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
